### Replication code
### Article: "Turnover: How lame-duck governments disrupt the bureaucracy and service delivery before leaving office"
### Author: Guillermo Toral (www.guillermotoral.com)
### Date: July 2023
### This file prepares the dataset on municipal employees
### This file uses the RAIS municipal employment datasets stored in datasets > downloaded > employment > rais_processed. These files, in turn, are created with the code at the beginning of this file, building on the original RAIS files distributed by the Brazilian government. Download instructions are the in the README file.
### R version, platform, and package versions reported at the end of the file

# Prepare the environment -------------------------------------------------

### This section of the code prepares the environment 

# Clean the environment
rm(list = ls())

# Install required packages if not previously installed
package_list <- c("tidyverse", "here", "lubridate", "xtable") 
packages_to_install <- package_list[!(package_list %in% installed.packages()[,"Package"])]
if(length(packages_to_install)>0){
  install.packages(packages_to_install)
}

# Load required packages
library(tidyverse)
library(here)
library(lubridate)
library(xtable)

# Set Working Directory to wherever this file is located.
setwd(here())

# Clean RAIS files --------------------------------------------------

# This section of the code cleans the original RAIS datasets, which can be downloaded from the Brazilian government server: ftp://ftp.mtps.gov.br/pdet/microdados/rais/ (see instructions in the README file)

# Indicators for executive municipal government employers: Municipal governments, + autarquias, fundações, e orgaos publicos autonomos do governo municipal, and a generic "municipio" that appears from 2016 onward
mun_govt_employers <- c(1031, 1120, 1155, 1180, 1244, 1279, 1309, 1333) 

# Codes for states (all but DF, the federal district, because it has no municipal elections)
estados <- c("AC", "AL", "AM", "AP", "BA", "CE", "ES", "GO", "MA", "MG", "MS", "MT", "PA", "PB", "PE", "PI", "PR", "RJ", "RN", "RO", "RR", "RS", "SC", "SE", "SP", "TO") 

# Years used in this paper
years <- c(2017, 2016, 2013, 2012, 2009, 2008, 2005, 2004)

# Clean RAIS datasets, select municipal employees, and bind all states together
for(j in c(1:length(years))){ 
  for(i in 1:length(estados)){
    r <- read_delim(paste0("../../datasets/downloaded/employment/rais_original/",years[j],"/",estados[i],years[j],".txt"),delim=";",locale = readr::locale(encoding = 'latin1'),guess_max = 1000000)
    e <- r %>%
      dplyr::filter(`Natureza Jurídica` %in% mun_govt_employers) %>% # This selects only jobs in municipal governments, based on the legal nature of the employer
      dplyr::select(cod_ibge = Município, 
                    legal_nature = `Natureza Jurídica`,
                    cbo02 = `CBO Ocupação 2002`,
                    job_type = `Tipo Vínculo`,
                    start_month = `Mês Admissão`,
                    entry_type = `Tipo Admissão`,
                    end_reason = `Motivo Desligamento`, 
                    end_month = `Mês Desligamento`,
                    mean_salary = `Vl Remun Média Nom`) %>%
      mutate(cod_ibge = as.numeric(cod_ibge), 
             legal_nature = as.numeric(legal_nature),
             cbo02 = as.numeric(cbo02),
             job_type = as.numeric(job_type),
             start_month = as.numeric(start_month),
             entry_type = as.numeric(entry_type),
             end_reason = as.numeric(end_reason), 
             end_month = as.numeric(end_month),
             mean_salary = as.numeric(gsub(",", ".", gsub("\\.", "", mean_salary))))
    if(i==1){
      ee <- e
    } else {
      ee <- bind_rows(ee,e)
    }
  }
  write_csv(ee, paste0("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_", years[j], ".csv")) # Export
}

# Build employment dataset -- all municipal employees --------------------------------------------
# 2004 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2004

e2004 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2004.csv")
et2004 <- e2004 %>% # Only temporary jobs
  subset(!(e2004$job_type %in% c(30,31)))
ec2004 <- e2004 %>% # Only tenured jobs
  subset(e2004$job_type %in% c(30,31))

e2005 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2005.csv")
et2005 <- e2005 %>% # Only temporary jobs
  subset(!(e2005$job_type %in% c(30,31)))
ec2005 <- e2005 %>% # Only tenured jobs
  subset(e2005$job_type %in% c(30,31))

# The letters "tem" correspond to temporary jobs, "con" to tenured jobs (for Portuguese "concursados")
# q14, q15 and q16 correspond to the 14th, 15th, and 16th quarter of a mayor's term, respectively (i.e., April - June, July - September, and October - December, respectively).
# q1 corresponds to the first quarter of the new administration (January to March)

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2004_here <- subset(e2004, e2004$cod_ibge == m$cod_ibge[i])
  e2005_here <- subset(e2005, e2005$cod_ibge == m$cod_ibge[i])
  et2004_here <- subset(et2004, et2004$cod_ibge == m$cod_ibge[i])
  et2005_here <- subset(et2005, et2005$cod_ibge == m$cod_ibge[i])
  ec2004_here <- subset(ec2004, ec2004$cod_ibge == m$cod_ibge[i])
  ec2005_here <- subset(ec2005, ec2005$cod_ibge == m$cod_ibge[i])
  if(nrow(e2004_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 3 & et2004_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 6 & et2004_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & et2004_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 3 & ec2004_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 6 & ec2004_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & ec2004_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2004_here[which(et2004_here$start_month < 10 & (et2004_here$end_month == 0 | et2004_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$start_month < 10 & (ec2004_here$end_month == 0 | ec2004_here$end_month > 6)),])
  }
  if(nrow(e2005_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$entry_type < 3 & et2005_here$start_month > 0 & et2005_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason %in% c(10,11) & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason == 21 & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$entry_type < 3 & ec2005_here$start_month > 0 & ec2005_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 10 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 21 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
  }
}

m2004 <- m

# 2008 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2008

e2008 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2008.csv")
et2008 <- e2008 %>%
  subset(!(e2008$job_type %in% c(30,31)))
ec2008 <- e2008 %>%
  subset(e2008$job_type %in% c(30,31))

e2009 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2009.csv")
et2009 <- e2009 %>%
  subset(!(e2009$job_type %in% c(30,31)))
ec2009 <- e2009 %>%
  subset(e2009$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2008_here <- subset(e2008, e2008$cod_ibge == m$cod_ibge[i])
  e2009_here <- subset(e2009, e2009$cod_ibge == m$cod_ibge[i])
  et2008_here <- subset(et2008, et2008$cod_ibge == m$cod_ibge[i])
  et2009_here <- subset(et2009, et2009$cod_ibge == m$cod_ibge[i])
  ec2008_here <- subset(ec2008, ec2008$cod_ibge == m$cod_ibge[i])
  ec2009_here <- subset(ec2009, ec2009$cod_ibge == m$cod_ibge[i])
  if(nrow(e2008_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 3 & et2008_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 6 & et2008_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & et2008_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 3 & ec2008_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 6 & ec2008_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & ec2008_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2008_here[which(et2008_here$start_month < 10 & (et2008_here$end_month == 0 | et2008_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$start_month < 10 & (ec2008_here$end_month == 0 | ec2008_here$end_month > 6)),])
  }
  if(nrow(e2009_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$entry_type < 3 & et2009_here$start_month > 0 & et2009_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason %in% c(10,11) & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason == 21 & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$entry_type < 3 & ec2009_here$start_month > 0 & ec2009_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 10 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 21 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
  }
}

m2008 <- m

# 2012 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2012

e2012 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2012.csv")
et2012 <- e2012 %>%
  subset(!(e2012$job_type %in% c(30,31)))
ec2012 <- e2012 %>%
  subset(e2012$job_type %in% c(30,31))

e2013 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2013.csv")
et2013 <- e2013 %>%
  subset(!(e2013$job_type %in% c(30,31)))
ec2013 <- e2013 %>%
  subset(e2013$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2012_here <- subset(e2012, e2012$cod_ibge == m$cod_ibge[i])
  e2013_here <- subset(e2013, e2013$cod_ibge == m$cod_ibge[i])
  et2012_here <- subset(et2012, et2012$cod_ibge == m$cod_ibge[i])
  et2013_here <- subset(et2013, et2013$cod_ibge == m$cod_ibge[i])
  ec2012_here <- subset(ec2012, ec2012$cod_ibge == m$cod_ibge[i])
  ec2013_here <- subset(ec2013, ec2013$cod_ibge == m$cod_ibge[i])
  if(nrow(e2012_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 3 & et2012_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 6 & et2012_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & et2012_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 3 & ec2012_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 6 & ec2012_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & ec2012_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2012_here[which(et2012_here$start_month < 10 & (et2012_here$end_month == 0 | et2012_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$start_month < 10 & (ec2012_here$end_month == 0 | ec2012_here$end_month > 6)),])
  }
  if(nrow(e2013_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$entry_type < 3 & et2013_here$start_month > 0 & et2013_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason %in% c(10,11) & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason == 21 & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$entry_type < 3 & ec2013_here$start_month > 0 & ec2013_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 10 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 21 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
  }
}

m2012 <- m

# 2016 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2016

e2016 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2016.csv")
et2016 <- e2016 %>%
  subset(!(e2016$job_type %in% c(30,31)))
ec2016 <- e2016 %>%
  subset(e2016$job_type %in% c(30,31))

e2017 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2017.csv")
et2017 <- e2017 %>%
  subset(!(e2017$job_type %in% c(30,31)))
ec2017 <- e2017 %>%
  subset(e2017$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2016_here <- subset(e2016, e2016$cod_ibge == m$cod_ibge[i])
  e2017_here <- subset(e2017, e2017$cod_ibge == m$cod_ibge[i])
  et2016_here <- subset(et2016, et2016$cod_ibge == m$cod_ibge[i])
  et2017_here <- subset(et2017, et2017$cod_ibge == m$cod_ibge[i])
  ec2016_here <- subset(ec2016, ec2016$cod_ibge == m$cod_ibge[i])
  ec2017_here <- subset(ec2017, ec2017$cod_ibge == m$cod_ibge[i])
  if(nrow(e2016_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 3 & et2016_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 6 & et2016_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & et2016_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 3 & ec2016_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 6 & ec2016_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & ec2016_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2016_here[which(et2016_here$start_month < 10 & (et2016_here$end_month == 0 | et2016_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$start_month < 10 & (ec2016_here$end_month == 0 | ec2016_here$end_month > 6)),])
  }
  if(nrow(e2017_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$entry_type < 3 & et2017_here$start_month > 0 & et2017_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason %in% c(10,11) & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason == 21 & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$entry_type < 3 & ec2017_here$start_month > 0 & ec2017_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 10 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 21 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
  }
}

m2016 <- m

# Bind and export ---------------------------------------------------------

e <- rbind(m2004, m2008, m2012, m2016)

write_csv(e, "../../datasets/analysis/employment_all_data.csv")

# Build employment dataset -- healthcare municipal employees --------------------------------------------

# These are the codes in CBO that correspond to healthcare professionals. Codes can be checked at https://www.ocupacoes.com.br/cbo-mte/
healthcare_largecats <- c(322, 223, 225, 515, 324) # These correspond to doctors, nurses, nurse technicians, health agents, diagnostics technicians, etc. 
healthcare_smallcats <- c(251510, 352210, 131205, 131210, 131215, 131220) # these add clinical psychologist, radiology technician, public health agent, managers of health facilities

# 2004 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2004

e2004 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2004.csv") %>%
  dplyr::filter(cbo02 %in% healthcare_smallcats | substr(cbo02,1,3) %in% healthcare_largecats)
et2004 <- e2004 %>% # Only temporary jobs
  subset(!(e2004$job_type %in% c(30,31)))
ec2004 <- e2004 %>% # Only tenured jobs
  subset(e2004$job_type %in% c(30,31))

e2005 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2005.csv") %>%
  dplyr::filter(cbo02 %in% healthcare_smallcats | substr(cbo02,1,3) %in% healthcare_largecats)
et2005 <- e2005 %>% # Only temporary jobs
  subset(!(e2005$job_type %in% c(30,31)))
ec2005 <- e2005 %>% # Only tenured jobs
  subset(e2005$job_type %in% c(30,31))

# The letters "tem" correspond to temporary jobs, "con" to tenured jobs (for Portuguese "concursados")
# q14, q15 and q16 correspond to the 14th, 15th, and 16th quarter of a mayor's term, respectively (i.e., April - June, July - September, and October - December, respectively).
# q1 corresponds to the first quarter of the new administration (January to March)

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2004_here <- subset(e2004, e2004$cod_ibge == m$cod_ibge[i])
  e2005_here <- subset(e2005, e2005$cod_ibge == m$cod_ibge[i])
  et2004_here <- subset(et2004, et2004$cod_ibge == m$cod_ibge[i])
  et2005_here <- subset(et2005, et2005$cod_ibge == m$cod_ibge[i])
  ec2004_here <- subset(ec2004, ec2004$cod_ibge == m$cod_ibge[i])
  ec2005_here <- subset(ec2005, ec2005$cod_ibge == m$cod_ibge[i])
  if(nrow(e2004_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 3 & et2004_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 6 & et2004_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & et2004_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 3 & ec2004_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 6 & ec2004_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & ec2004_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2004_here[which(et2004_here$start_month < 10 & (et2004_here$end_month == 0 | et2004_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$start_month < 10 & (ec2004_here$end_month == 0 | ec2004_here$end_month > 6)),])
  }
  if(nrow(e2005_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$entry_type < 3 & et2005_here$start_month > 0 & et2005_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason %in% c(10,11) & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason == 21 & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$entry_type < 3 & ec2005_here$start_month > 0 & ec2005_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 10 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 21 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
  }
}

m2004 <- m

# 2008 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2008

e2008 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2008.csv") %>%
  dplyr::filter(cbo02 %in% healthcare_smallcats | substr(cbo02,1,3) %in% healthcare_largecats)
et2008 <- e2008 %>%
  subset(!(e2008$job_type %in% c(30,31)))
ec2008 <- e2008 %>%
  subset(e2008$job_type %in% c(30,31))

e2009 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2009.csv") %>%
  dplyr::filter(cbo02 %in% healthcare_smallcats | substr(cbo02,1,3) %in% healthcare_largecats)
et2009 <- e2009 %>%
  subset(!(e2009$job_type %in% c(30,31)))
ec2009 <- e2009 %>%
  subset(e2009$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2008_here <- subset(e2008, e2008$cod_ibge == m$cod_ibge[i])
  e2009_here <- subset(e2009, e2009$cod_ibge == m$cod_ibge[i])
  et2008_here <- subset(et2008, et2008$cod_ibge == m$cod_ibge[i])
  et2009_here <- subset(et2009, et2009$cod_ibge == m$cod_ibge[i])
  ec2008_here <- subset(ec2008, ec2008$cod_ibge == m$cod_ibge[i])
  ec2009_here <- subset(ec2009, ec2009$cod_ibge == m$cod_ibge[i])
  if(nrow(e2008_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 3 & et2008_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 6 & et2008_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & et2008_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 3 & ec2008_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 6 & ec2008_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & ec2008_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2008_here[which(et2008_here$start_month < 10 & (et2008_here$end_month == 0 | et2008_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$start_month < 10 & (ec2008_here$end_month == 0 | ec2008_here$end_month > 6)),])
  }
  if(nrow(e2009_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$entry_type < 3 & et2009_here$start_month > 0 & et2009_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason %in% c(10,11) & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason == 21 & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$entry_type < 3 & ec2009_here$start_month > 0 & ec2009_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 10 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 21 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
  }
}

m2008 <- m

# 2012 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2012

e2012 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2012.csv") %>%
  dplyr::filter(cbo02 %in% healthcare_smallcats | substr(cbo02,1,3) %in% healthcare_largecats)
et2012 <- e2012 %>%
  subset(!(e2012$job_type %in% c(30,31)))
ec2012 <- e2012 %>%
  subset(e2012$job_type %in% c(30,31))

e2013 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2013.csv") %>%
  dplyr::filter(cbo02 %in% healthcare_smallcats | substr(cbo02,1,3) %in% healthcare_largecats)
et2013 <- e2013 %>%
  subset(!(e2013$job_type %in% c(30,31)))
ec2013 <- e2013 %>%
  subset(e2013$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2012_here <- subset(e2012, e2012$cod_ibge == m$cod_ibge[i])
  e2013_here <- subset(e2013, e2013$cod_ibge == m$cod_ibge[i])
  et2012_here <- subset(et2012, et2012$cod_ibge == m$cod_ibge[i])
  et2013_here <- subset(et2013, et2013$cod_ibge == m$cod_ibge[i])
  ec2012_here <- subset(ec2012, ec2012$cod_ibge == m$cod_ibge[i])
  ec2013_here <- subset(ec2013, ec2013$cod_ibge == m$cod_ibge[i])
  if(nrow(e2012_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 3 & et2012_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 6 & et2012_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & et2012_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 3 & ec2012_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 6 & ec2012_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & ec2012_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2012_here[which(et2012_here$start_month < 10 & (et2012_here$end_month == 0 | et2012_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$start_month < 10 & (ec2012_here$end_month == 0 | ec2012_here$end_month > 6)),])
  }
  if(nrow(e2013_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$entry_type < 3 & et2013_here$start_month > 0 & et2013_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason %in% c(10,11) & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason == 21 & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$entry_type < 3 & ec2013_here$start_month > 0 & ec2013_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 10 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 21 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
  }
}

m2012 <- m

# 2016 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2016

e2016 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2016.csv") %>%
  dplyr::filter(cbo02 %in% healthcare_smallcats | substr(cbo02,1,3) %in% healthcare_largecats)
et2016 <- e2016 %>%
  subset(!(e2016$job_type %in% c(30,31)))
ec2016 <- e2016 %>%
  subset(e2016$job_type %in% c(30,31))

e2017 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2017.csv") %>%
  dplyr::filter(cbo02 %in% healthcare_smallcats | substr(cbo02,1,3) %in% healthcare_largecats)
et2017 <- e2017 %>%
  subset(!(e2017$job_type %in% c(30,31)))
ec2017 <- e2017 %>%
  subset(e2017$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2016_here <- subset(e2016, e2016$cod_ibge == m$cod_ibge[i])
  e2017_here <- subset(e2017, e2017$cod_ibge == m$cod_ibge[i])
  et2016_here <- subset(et2016, et2016$cod_ibge == m$cod_ibge[i])
  et2017_here <- subset(et2017, et2017$cod_ibge == m$cod_ibge[i])
  ec2016_here <- subset(ec2016, ec2016$cod_ibge == m$cod_ibge[i])
  ec2017_here <- subset(ec2017, ec2017$cod_ibge == m$cod_ibge[i])
  if(nrow(e2016_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 3 & et2016_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 6 & et2016_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & et2016_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 3 & ec2016_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 6 & ec2016_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & ec2016_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2016_here[which(et2016_here$start_month < 10 & (et2016_here$end_month == 0 | et2016_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$start_month < 10 & (ec2016_here$end_month == 0 | ec2016_here$end_month > 6)),])
  }
  if(nrow(e2017_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$entry_type < 3 & et2017_here$start_month > 0 & et2017_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason %in% c(10,11) & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason == 21 & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$entry_type < 3 & ec2017_here$start_month > 0 & ec2017_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 10 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 21 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
  }
}

m2016 <- m

# Bind and export ---------------------------------------------------------

e <- rbind(m2004, m2008, m2012, m2016)

write_csv(e, "../../datasets/analysis/employment_healthcare_data.csv")

# Build employment dataset -- education municipal employees --------------------------------------------

# These are the codes in CBO that correspond to education professionals. Codes can be checked at https://www.ocupacoes.com.br/cbo-mte/
education_largecats <- c(23, 33) # 23 are regular education professionals, 33 are those with no higher education

# 2004 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2004

e2004 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2004.csv") %>%
  dplyr::filter(substr(cbo02,1,2) %in% education_largecats)
et2004 <- e2004 %>% # Only temporary jobs
  subset(!(e2004$job_type %in% c(30,31)))
ec2004 <- e2004 %>% # Only tenured jobs
  subset(e2004$job_type %in% c(30,31))

e2005 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2005.csv") %>%
  dplyr::filter(substr(cbo02,1,2) %in% education_largecats)
et2005 <- e2005 %>% # Only temporary jobs
  subset(!(e2005$job_type %in% c(30,31)))
ec2005 <- e2005 %>% # Only tenured jobs
  subset(e2005$job_type %in% c(30,31))

# The letters "tem" correspond to temporary jobs, "con" to tenured jobs (for Portuguese "concursados")
# q14, q15 and q16 correspond to the 14th, 15th, and 16th quarter of a mayor's term, respectively (i.e., April - June, July - September, and October - December, respectively).
# q1 corresponds to the first quarter of the new administration (January to March)

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2004_here <- subset(e2004, e2004$cod_ibge == m$cod_ibge[i])
  e2005_here <- subset(e2005, e2005$cod_ibge == m$cod_ibge[i])
  et2004_here <- subset(et2004, et2004$cod_ibge == m$cod_ibge[i])
  et2005_here <- subset(et2005, et2005$cod_ibge == m$cod_ibge[i])
  ec2004_here <- subset(ec2004, ec2004$cod_ibge == m$cod_ibge[i])
  ec2005_here <- subset(ec2005, ec2005$cod_ibge == m$cod_ibge[i])
  if(nrow(e2004_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 3 & et2004_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 6 & et2004_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & et2004_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 3 & ec2004_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 6 & ec2004_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & ec2004_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2004_here[which(et2004_here$start_month < 10 & (et2004_here$end_month == 0 | et2004_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$start_month < 10 & (ec2004_here$end_month == 0 | ec2004_here$end_month > 6)),])
  }
  if(nrow(e2005_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$entry_type < 3 & et2005_here$start_month > 0 & et2005_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason %in% c(10,11) & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason == 21 & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$entry_type < 3 & ec2005_here$start_month > 0 & ec2005_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 10 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 21 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
  }
}

m2004 <- m

# 2008 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2008

e2008 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2008.csv") %>%
  dplyr::filter(substr(cbo02,1,2) %in% education_largecats)
et2008 <- e2008 %>%
  subset(!(e2008$job_type %in% c(30,31)))
ec2008 <- e2008 %>%
  subset(e2008$job_type %in% c(30,31))

e2009 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2009.csv") %>%
  dplyr::filter(substr(cbo02,1,2) %in% education_largecats)
et2009 <- e2009 %>%
  subset(!(e2009$job_type %in% c(30,31)))
ec2009 <- e2009 %>%
  subset(e2009$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2008_here <- subset(e2008, e2008$cod_ibge == m$cod_ibge[i])
  e2009_here <- subset(e2009, e2009$cod_ibge == m$cod_ibge[i])
  et2008_here <- subset(et2008, et2008$cod_ibge == m$cod_ibge[i])
  et2009_here <- subset(et2009, et2009$cod_ibge == m$cod_ibge[i])
  ec2008_here <- subset(ec2008, ec2008$cod_ibge == m$cod_ibge[i])
  ec2009_here <- subset(ec2009, ec2009$cod_ibge == m$cod_ibge[i])
  if(nrow(e2008_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 3 & et2008_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 6 & et2008_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & et2008_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 3 & ec2008_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 6 & ec2008_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & ec2008_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2008_here[which(et2008_here$start_month < 10 & (et2008_here$end_month == 0 | et2008_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$start_month < 10 & (ec2008_here$end_month == 0 | ec2008_here$end_month > 6)),])
  }
  if(nrow(e2009_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$entry_type < 3 & et2009_here$start_month > 0 & et2009_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason %in% c(10,11) & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason == 21 & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$entry_type < 3 & ec2009_here$start_month > 0 & ec2009_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 10 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 21 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
  }
}

m2008 <- m

# 2012 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2012

e2012 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2012.csv") %>%
  dplyr::filter(substr(cbo02,1,2) %in% education_largecats)
et2012 <- e2012 %>%
  subset(!(e2012$job_type %in% c(30,31)))
ec2012 <- e2012 %>%
  subset(e2012$job_type %in% c(30,31))

e2013 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2013.csv") %>%
  dplyr::filter(substr(cbo02,1,2) %in% education_largecats)
et2013 <- e2013 %>%
  subset(!(e2013$job_type %in% c(30,31)))
ec2013 <- e2013 %>%
  subset(e2013$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2012_here <- subset(e2012, e2012$cod_ibge == m$cod_ibge[i])
  e2013_here <- subset(e2013, e2013$cod_ibge == m$cod_ibge[i])
  et2012_here <- subset(et2012, et2012$cod_ibge == m$cod_ibge[i])
  et2013_here <- subset(et2013, et2013$cod_ibge == m$cod_ibge[i])
  ec2012_here <- subset(ec2012, ec2012$cod_ibge == m$cod_ibge[i])
  ec2013_here <- subset(ec2013, ec2013$cod_ibge == m$cod_ibge[i])
  if(nrow(e2012_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 3 & et2012_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 6 & et2012_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & et2012_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 3 & ec2012_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 6 & ec2012_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & ec2012_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2012_here[which(et2012_here$start_month < 10 & (et2012_here$end_month == 0 | et2012_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$start_month < 10 & (ec2012_here$end_month == 0 | ec2012_here$end_month > 6)),])
  }
  if(nrow(e2013_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$entry_type < 3 & et2013_here$start_month > 0 & et2013_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason %in% c(10,11) & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason == 21 & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$entry_type < 3 & ec2013_here$start_month > 0 & ec2013_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 10 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 21 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
  }
}

m2012 <- m

# 2016 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2016

e2016 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2016.csv") %>%
  dplyr::filter(substr(cbo02,1,2) %in% education_largecats)
et2016 <- e2016 %>%
  subset(!(e2016$job_type %in% c(30,31)))
ec2016 <- e2016 %>%
  subset(e2016$job_type %in% c(30,31))

e2017 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2017.csv") %>%
  dplyr::filter(substr(cbo02,1,2) %in% education_largecats)
et2017 <- e2017 %>%
  subset(!(e2017$job_type %in% c(30,31)))
ec2017 <- e2017 %>%
  subset(e2017$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2016_here <- subset(e2016, e2016$cod_ibge == m$cod_ibge[i])
  e2017_here <- subset(e2017, e2017$cod_ibge == m$cod_ibge[i])
  et2016_here <- subset(et2016, et2016$cod_ibge == m$cod_ibge[i])
  et2017_here <- subset(et2017, et2017$cod_ibge == m$cod_ibge[i])
  ec2016_here <- subset(ec2016, ec2016$cod_ibge == m$cod_ibge[i])
  ec2017_here <- subset(ec2017, ec2017$cod_ibge == m$cod_ibge[i])
  if(nrow(e2016_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 3 & et2016_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 6 & et2016_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & et2016_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 3 & ec2016_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 6 & ec2016_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & ec2016_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2016_here[which(et2016_here$start_month < 10 & (et2016_here$end_month == 0 | et2016_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$start_month < 10 & (ec2016_here$end_month == 0 | ec2016_here$end_month > 6)),])
  }
  if(nrow(e2017_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$entry_type < 3 & et2017_here$start_month > 0 & et2017_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason %in% c(10,11) & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason == 21 & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$entry_type < 3 & ec2017_here$start_month > 0 & ec2017_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 10 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 21 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
  }
}

m2016 <- m

# Bind and export ---------------------------------------------------------

e <- rbind(m2004, m2008, m2012, m2016)

write_csv(e, "../../datasets/analysis/employment_education_data.csv")

# Build employment dataset -- manager municipal employees --------------------------------------------

# These are the codes in CBO that correspond to managers. Codes can be checked at https://www.ocupacoes.com.br/cbo-mte/
managers_largecats <- c(1) # start with 1 all MEMBROS SUPERIORES DO PODER PÚBLICO, DIRIGENTES DE ORGANIZAÇÕES DE INTERESSE PÚBLICO E DE EMPRESAS, GERENTES

# 2004 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2004

e2004 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2004.csv") %>%
  dplyr::filter(substr(cbo02,1,1) %in% managers_largecats)
et2004 <- e2004 %>% # Only temporary jobs
  subset(!(e2004$job_type %in% c(30,31)))
ec2004 <- e2004 %>% # Only tenured jobs
  subset(e2004$job_type %in% c(30,31))

e2005 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2005.csv") %>%
  dplyr::filter(substr(cbo02,1,1) %in% managers_largecats)
et2005 <- e2005 %>% # Only temporary jobs
  subset(!(e2005$job_type %in% c(30,31)))
ec2005 <- e2005 %>% # Only tenured jobs
  subset(e2005$job_type %in% c(30,31))

# The letters "tem" correspond to temporary jobs, "con" to tenured jobs (for Portuguese "concursados")
# q14, q15 and q16 correspond to the 14th, 15th, and 16th quarter of a mayor's term, respectively (i.e., April - June, July - September, and October - December, respectively).
# q1 corresponds to the first quarter of the new administration (January to March)

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2004_here <- subset(e2004, e2004$cod_ibge == m$cod_ibge[i])
  e2005_here <- subset(e2005, e2005$cod_ibge == m$cod_ibge[i])
  et2004_here <- subset(et2004, et2004$cod_ibge == m$cod_ibge[i])
  et2005_here <- subset(et2005, et2005$cod_ibge == m$cod_ibge[i])
  ec2004_here <- subset(ec2004, ec2004$cod_ibge == m$cod_ibge[i])
  ec2005_here <- subset(ec2005, ec2005$cod_ibge == m$cod_ibge[i])
  if(nrow(e2004_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 3 & et2004_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 6 & et2004_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & et2004_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 3 & ec2004_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 6 & ec2004_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & ec2004_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2004_here[which(et2004_here$start_month < 10 & (et2004_here$end_month == 0 | et2004_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$start_month < 10 & (ec2004_here$end_month == 0 | ec2004_here$end_month > 6)),])
  }
  if(nrow(e2005_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$entry_type < 3 & et2005_here$start_month > 0 & et2005_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason %in% c(10,11) & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason == 21 & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$entry_type < 3 & ec2005_here$start_month > 0 & ec2005_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 10 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 21 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
  }
}

m2004 <- m

# 2008 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2008

e2008 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2008.csv") %>%
  dplyr::filter(substr(cbo02,1,1) %in% managers_largecats)
et2008 <- e2008 %>%
  subset(!(e2008$job_type %in% c(30,31)))
ec2008 <- e2008 %>%
  subset(e2008$job_type %in% c(30,31))

e2009 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2009.csv") %>%
  dplyr::filter(substr(cbo02,1,1) %in% managers_largecats)
et2009 <- e2009 %>%
  subset(!(e2009$job_type %in% c(30,31)))
ec2009 <- e2009 %>%
  subset(e2009$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2008_here <- subset(e2008, e2008$cod_ibge == m$cod_ibge[i])
  e2009_here <- subset(e2009, e2009$cod_ibge == m$cod_ibge[i])
  et2008_here <- subset(et2008, et2008$cod_ibge == m$cod_ibge[i])
  et2009_here <- subset(et2009, et2009$cod_ibge == m$cod_ibge[i])
  ec2008_here <- subset(ec2008, ec2008$cod_ibge == m$cod_ibge[i])
  ec2009_here <- subset(ec2009, ec2009$cod_ibge == m$cod_ibge[i])
  if(nrow(e2008_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 3 & et2008_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 6 & et2008_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & et2008_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 3 & ec2008_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 6 & ec2008_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & ec2008_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2008_here[which(et2008_here$start_month < 10 & (et2008_here$end_month == 0 | et2008_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$start_month < 10 & (ec2008_here$end_month == 0 | ec2008_here$end_month > 6)),])
  }
  if(nrow(e2009_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$entry_type < 3 & et2009_here$start_month > 0 & et2009_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason %in% c(10,11) & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason == 21 & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$entry_type < 3 & ec2009_here$start_month > 0 & ec2009_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 10 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 21 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
  }
}

m2008 <- m

# 2012 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2012

e2012 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2012.csv") %>%
  dplyr::filter(substr(cbo02,1,1) %in% managers_largecats)
et2012 <- e2012 %>%
  subset(!(e2012$job_type %in% c(30,31)))
ec2012 <- e2012 %>%
  subset(e2012$job_type %in% c(30,31))

e2013 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2013.csv") %>%
  dplyr::filter(substr(cbo02,1,1) %in% managers_largecats)
et2013 <- e2013 %>%
  subset(!(e2013$job_type %in% c(30,31)))
ec2013 <- e2013 %>%
  subset(e2013$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2012_here <- subset(e2012, e2012$cod_ibge == m$cod_ibge[i])
  e2013_here <- subset(e2013, e2013$cod_ibge == m$cod_ibge[i])
  et2012_here <- subset(et2012, et2012$cod_ibge == m$cod_ibge[i])
  et2013_here <- subset(et2013, et2013$cod_ibge == m$cod_ibge[i])
  ec2012_here <- subset(ec2012, ec2012$cod_ibge == m$cod_ibge[i])
  ec2013_here <- subset(ec2013, ec2013$cod_ibge == m$cod_ibge[i])
  if(nrow(e2012_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 3 & et2012_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 6 & et2012_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & et2012_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 3 & ec2012_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 6 & ec2012_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & ec2012_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2012_here[which(et2012_here$start_month < 10 & (et2012_here$end_month == 0 | et2012_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$start_month < 10 & (ec2012_here$end_month == 0 | ec2012_here$end_month > 6)),])
  }
  if(nrow(e2013_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$entry_type < 3 & et2013_here$start_month > 0 & et2013_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason %in% c(10,11) & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason == 21 & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$entry_type < 3 & ec2013_here$start_month > 0 & ec2013_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 10 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 21 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
  }
}

m2012 <- m

# 2016 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2016

e2016 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2016.csv") %>%
  dplyr::filter(substr(cbo02,1,1) %in% managers_largecats)
et2016 <- e2016 %>%
  subset(!(e2016$job_type %in% c(30,31)))
ec2016 <- e2016 %>%
  subset(e2016$job_type %in% c(30,31))

e2017 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2017.csv") %>%
  dplyr::filter(substr(cbo02,1,1) %in% managers_largecats)
et2017 <- e2017 %>%
  subset(!(e2017$job_type %in% c(30,31)))
ec2017 <- e2017 %>%
  subset(e2017$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2016_here <- subset(e2016, e2016$cod_ibge == m$cod_ibge[i])
  e2017_here <- subset(e2017, e2017$cod_ibge == m$cod_ibge[i])
  et2016_here <- subset(et2016, et2016$cod_ibge == m$cod_ibge[i])
  et2017_here <- subset(et2017, et2017$cod_ibge == m$cod_ibge[i])
  ec2016_here <- subset(ec2016, ec2016$cod_ibge == m$cod_ibge[i])
  ec2017_here <- subset(ec2017, ec2017$cod_ibge == m$cod_ibge[i])
  if(nrow(e2016_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 3 & et2016_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 6 & et2016_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & et2016_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 3 & ec2016_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 6 & ec2016_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & ec2016_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2016_here[which(et2016_here$start_month < 10 & (et2016_here$end_month == 0 | et2016_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$start_month < 10 & (ec2016_here$end_month == 0 | ec2016_here$end_month > 6)),])
  }
  if(nrow(e2017_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$entry_type < 3 & et2017_here$start_month > 0 & et2017_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason %in% c(10,11) & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason == 21 & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$entry_type < 3 & ec2017_here$start_month > 0 & ec2017_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 10 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 21 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
  }
}

m2016 <- m

# Bind and export ---------------------------------------------------------

e <- rbind(m2004, m2008, m2012, m2016)

write_csv(e, "../../datasets/analysis/employment_managers_data.csv")

# Build employment dataset -- non-manager municipal employees --------------------------------------------

# These are the codes in CBO that correspond to managers. Codes can be checked at https://www.ocupacoes.com.br/cbo-mte/
managers_largecats <- c(1) # start with 1 all MEMBROS SUPERIORES DO PODER PÚBLICO, DIRIGENTES DE ORGANIZAÇÕES DE INTERESSE PÚBLICO E DE EMPRESAS, GERENTES

# 2004 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2004

e2004 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2004.csv") %>%
  dplyr::filter(substr(cbo02,1,1) !=1)
et2004 <- e2004 %>% # Only temporary jobs
  subset(!(e2004$job_type %in% c(30,31)))
ec2004 <- e2004 %>% # Only tenured jobs
  subset(e2004$job_type %in% c(30,31))

e2005 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2005.csv") %>%
  dplyr::filter(substr(cbo02,1,1) !=1)
et2005 <- e2005 %>% # Only temporary jobs
  subset(!(e2005$job_type %in% c(30,31)))
ec2005 <- e2005 %>% # Only tenured jobs
  subset(e2005$job_type %in% c(30,31))

# The letters "tem" correspond to temporary jobs, "con" to tenured jobs (for Portuguese "concursados")
# q14, q15 and q16 correspond to the 14th, 15th, and 16th quarter of a mayor's term, respectively (i.e., April - June, July - September, and October - December, respectively).
# q1 corresponds to the first quarter of the new administration (January to March)

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2004_here <- subset(e2004, e2004$cod_ibge == m$cod_ibge[i])
  e2005_here <- subset(e2005, e2005$cod_ibge == m$cod_ibge[i])
  et2004_here <- subset(et2004, et2004$cod_ibge == m$cod_ibge[i])
  et2005_here <- subset(et2005, et2005$cod_ibge == m$cod_ibge[i])
  ec2004_here <- subset(ec2004, ec2004$cod_ibge == m$cod_ibge[i])
  ec2005_here <- subset(ec2005, ec2005$cod_ibge == m$cod_ibge[i])
  if(nrow(e2004_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 3 & et2004_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 6 & et2004_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & et2004_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 3 & ec2004_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 6 & ec2004_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & ec2004_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2004_here[which(et2004_here$start_month < 10 & (et2004_here$end_month == 0 | et2004_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$start_month < 10 & (ec2004_here$end_month == 0 | ec2004_here$end_month > 6)),])
  }
  if(nrow(e2005_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$entry_type < 3 & et2005_here$start_month > 0 & et2005_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason %in% c(10,11) & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason == 21 & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$entry_type < 3 & ec2005_here$start_month > 0 & ec2005_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 10 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 21 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
  }
}

m2004 <- m

# 2008 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2008

e2008 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2008.csv") %>%
  dplyr::filter(substr(cbo02,1,1) !=1)
et2008 <- e2008 %>%
  subset(!(e2008$job_type %in% c(30,31)))
ec2008 <- e2008 %>%
  subset(e2008$job_type %in% c(30,31))

e2009 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2009.csv") %>%
  dplyr::filter(substr(cbo02,1,1) !=1)
et2009 <- e2009 %>%
  subset(!(e2009$job_type %in% c(30,31)))
ec2009 <- e2009 %>%
  subset(e2009$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2008_here <- subset(e2008, e2008$cod_ibge == m$cod_ibge[i])
  e2009_here <- subset(e2009, e2009$cod_ibge == m$cod_ibge[i])
  et2008_here <- subset(et2008, et2008$cod_ibge == m$cod_ibge[i])
  et2009_here <- subset(et2009, et2009$cod_ibge == m$cod_ibge[i])
  ec2008_here <- subset(ec2008, ec2008$cod_ibge == m$cod_ibge[i])
  ec2009_here <- subset(ec2009, ec2009$cod_ibge == m$cod_ibge[i])
  if(nrow(e2008_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 3 & et2008_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 6 & et2008_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & et2008_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 3 & ec2008_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 6 & ec2008_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & ec2008_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2008_here[which(et2008_here$start_month < 10 & (et2008_here$end_month == 0 | et2008_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$start_month < 10 & (ec2008_here$end_month == 0 | ec2008_here$end_month > 6)),])
  }
  if(nrow(e2009_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$entry_type < 3 & et2009_here$start_month > 0 & et2009_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason %in% c(10,11) & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason == 21 & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$entry_type < 3 & ec2009_here$start_month > 0 & ec2009_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 10 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 21 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
  }
}

m2008 <- m

# 2012 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2012

e2012 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2012.csv") %>%
  dplyr::filter(substr(cbo02,1,1) !=1)
et2012 <- e2012 %>%
  subset(!(e2012$job_type %in% c(30,31)))
ec2012 <- e2012 %>%
  subset(e2012$job_type %in% c(30,31))

e2013 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2013.csv") %>%
  dplyr::filter(substr(cbo02,1,1) !=1)
et2013 <- e2013 %>%
  subset(!(e2013$job_type %in% c(30,31)))
ec2013 <- e2013 %>%
  subset(e2013$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2012_here <- subset(e2012, e2012$cod_ibge == m$cod_ibge[i])
  e2013_here <- subset(e2013, e2013$cod_ibge == m$cod_ibge[i])
  et2012_here <- subset(et2012, et2012$cod_ibge == m$cod_ibge[i])
  et2013_here <- subset(et2013, et2013$cod_ibge == m$cod_ibge[i])
  ec2012_here <- subset(ec2012, ec2012$cod_ibge == m$cod_ibge[i])
  ec2013_here <- subset(ec2013, ec2013$cod_ibge == m$cod_ibge[i])
  if(nrow(e2012_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 3 & et2012_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 6 & et2012_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & et2012_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 3 & ec2012_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 6 & ec2012_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & ec2012_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2012_here[which(et2012_here$start_month < 10 & (et2012_here$end_month == 0 | et2012_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$start_month < 10 & (ec2012_here$end_month == 0 | ec2012_here$end_month > 6)),])
  }
  if(nrow(e2013_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$entry_type < 3 & et2013_here$start_month > 0 & et2013_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason %in% c(10,11) & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason == 21 & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$entry_type < 3 & ec2013_here$start_month > 0 & ec2013_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 10 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 21 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
  }
}

m2012 <- m

# 2016 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2016

e2016 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2016.csv") %>%
  dplyr::filter(substr(cbo02,1,1) !=1)
et2016 <- e2016 %>%
  subset(!(e2016$job_type %in% c(30,31)))
ec2016 <- e2016 %>%
  subset(e2016$job_type %in% c(30,31))

e2017 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2017.csv") %>%
  dplyr::filter(substr(cbo02,1,1) !=1)
et2017 <- e2017 %>%
  subset(!(e2017$job_type %in% c(30,31)))
ec2017 <- e2017 %>%
  subset(e2017$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2016_here <- subset(e2016, e2016$cod_ibge == m$cod_ibge[i])
  e2017_here <- subset(e2017, e2017$cod_ibge == m$cod_ibge[i])
  et2016_here <- subset(et2016, et2016$cod_ibge == m$cod_ibge[i])
  et2017_here <- subset(et2017, et2017$cod_ibge == m$cod_ibge[i])
  ec2016_here <- subset(ec2016, ec2016$cod_ibge == m$cod_ibge[i])
  ec2017_here <- subset(ec2017, ec2017$cod_ibge == m$cod_ibge[i])
  if(nrow(e2016_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 3 & et2016_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 6 & et2016_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & et2016_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 3 & ec2016_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 6 & ec2016_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & ec2016_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2016_here[which(et2016_here$start_month < 10 & (et2016_here$end_month == 0 | et2016_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$start_month < 10 & (ec2016_here$end_month == 0 | ec2016_here$end_month > 6)),])
  }
  if(nrow(e2017_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$entry_type < 3 & et2017_here$start_month > 0 & et2017_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason %in% c(10,11) & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason == 21 & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$entry_type < 3 & ec2017_here$start_month > 0 & ec2017_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 10 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 21 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
  }
}

m2016 <- m

# Bind and export ---------------------------------------------------------

e <- rbind(m2004, m2008, m2012, m2016)

write_csv(e, "../../datasets/analysis/employment_nonmanagers_data.csv")

# Build employment dataset -- low-pay municipal employees --------------------------------------------

# These are the codes in CBO that correspond to managers. Codes can be checked at https://www.ocupacoes.com.br/cbo-mte/
managers_largecats <- c(1) # start with 1 all MEMBROS SUPERIORES DO PODER PÚBLICO, DIRIGENTES DE ORGANIZAÇÕES DE INTERESSE PÚBLICO E DE EMPRESAS, GERENTES

# 2004 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2004

e2004 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2004.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2004 <- e2004 %>% # Only temporary jobs
  subset(!(e2004$job_type %in% c(30,31)))
ec2004 <- e2004 %>% # Only tenured jobs
  subset(e2004$job_type %in% c(30,31))

e2005 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2005.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2005 <- e2005 %>% # Only temporary jobs
  subset(!(e2005$job_type %in% c(30,31)))
ec2005 <- e2005 %>% # Only tenured jobs
  subset(e2005$job_type %in% c(30,31))

# The letters "tem" correspond to temporary jobs, "con" to tenured jobs (for Portuguese "concursados")
# q14, q15 and q16 correspond to the 14th, 15th, and 16th quarter of a mayor's term, respectively (i.e., April - June, July - September, and October - December, respectively).
# q1 corresponds to the first quarter of the new administration (January to March)

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2004_here <- subset(e2004, e2004$cod_ibge == m$cod_ibge[i])
  e2005_here <- subset(e2005, e2005$cod_ibge == m$cod_ibge[i])
  et2004_here <- subset(et2004, et2004$cod_ibge == m$cod_ibge[i])
  et2005_here <- subset(et2005, et2005$cod_ibge == m$cod_ibge[i])
  ec2004_here <- subset(ec2004, ec2004$cod_ibge == m$cod_ibge[i])
  ec2005_here <- subset(ec2005, ec2005$cod_ibge == m$cod_ibge[i])
  if(nrow(e2004_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 3 & et2004_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 6 & et2004_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & et2004_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 3 & ec2004_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 6 & ec2004_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & ec2004_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2004_here[which(et2004_here$start_month < 10 & (et2004_here$end_month == 0 | et2004_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$start_month < 10 & (ec2004_here$end_month == 0 | ec2004_here$end_month > 6)),])
  }
  if(nrow(e2005_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$entry_type < 3 & et2005_here$start_month > 0 & et2005_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason %in% c(10,11) & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason == 21 & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$entry_type < 3 & ec2005_here$start_month > 0 & ec2005_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 10 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 21 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
  }
}

m2004 <- m

# 2008 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2008

e2008 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2008.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2008 <- e2008 %>%
  subset(!(e2008$job_type %in% c(30,31)))
ec2008 <- e2008 %>%
  subset(e2008$job_type %in% c(30,31))

e2009 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2009.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2009 <- e2009 %>%
  subset(!(e2009$job_type %in% c(30,31)))
ec2009 <- e2009 %>%
  subset(e2009$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2008_here <- subset(e2008, e2008$cod_ibge == m$cod_ibge[i])
  e2009_here <- subset(e2009, e2009$cod_ibge == m$cod_ibge[i])
  et2008_here <- subset(et2008, et2008$cod_ibge == m$cod_ibge[i])
  et2009_here <- subset(et2009, et2009$cod_ibge == m$cod_ibge[i])
  ec2008_here <- subset(ec2008, ec2008$cod_ibge == m$cod_ibge[i])
  ec2009_here <- subset(ec2009, ec2009$cod_ibge == m$cod_ibge[i])
  if(nrow(e2008_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 3 & et2008_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 6 & et2008_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & et2008_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 3 & ec2008_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 6 & ec2008_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & ec2008_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2008_here[which(et2008_here$start_month < 10 & (et2008_here$end_month == 0 | et2008_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$start_month < 10 & (ec2008_here$end_month == 0 | ec2008_here$end_month > 6)),])
  }
  if(nrow(e2009_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$entry_type < 3 & et2009_here$start_month > 0 & et2009_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason %in% c(10,11) & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason == 21 & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$entry_type < 3 & ec2009_here$start_month > 0 & ec2009_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 10 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 21 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
  }
}

m2008 <- m

# 2012 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2012

e2012 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2012.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2012 <- e2012 %>%
  subset(!(e2012$job_type %in% c(30,31)))
ec2012 <- e2012 %>%
  subset(e2012$job_type %in% c(30,31))

e2013 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2013.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2013 <- e2013 %>%
  subset(!(e2013$job_type %in% c(30,31)))
ec2013 <- e2013 %>%
  subset(e2013$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2012_here <- subset(e2012, e2012$cod_ibge == m$cod_ibge[i])
  e2013_here <- subset(e2013, e2013$cod_ibge == m$cod_ibge[i])
  et2012_here <- subset(et2012, et2012$cod_ibge == m$cod_ibge[i])
  et2013_here <- subset(et2013, et2013$cod_ibge == m$cod_ibge[i])
  ec2012_here <- subset(ec2012, ec2012$cod_ibge == m$cod_ibge[i])
  ec2013_here <- subset(ec2013, ec2013$cod_ibge == m$cod_ibge[i])
  if(nrow(e2012_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 3 & et2012_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 6 & et2012_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & et2012_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 3 & ec2012_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 6 & ec2012_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & ec2012_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2012_here[which(et2012_here$start_month < 10 & (et2012_here$end_month == 0 | et2012_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$start_month < 10 & (ec2012_here$end_month == 0 | ec2012_here$end_month > 6)),])
  }
  if(nrow(e2013_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$entry_type < 3 & et2013_here$start_month > 0 & et2013_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason %in% c(10,11) & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason == 21 & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$entry_type < 3 & ec2013_here$start_month > 0 & ec2013_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 10 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 21 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
  }
}

m2012 <- m

# 2016 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2016

e2016 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2016.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2016 <- e2016 %>%
  subset(!(e2016$job_type %in% c(30,31)))
ec2016 <- e2016 %>%
  subset(e2016$job_type %in% c(30,31))

e2017 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2017.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2017 <- e2017 %>%
  subset(!(e2017$job_type %in% c(30,31)))
ec2017 <- e2017 %>%
  subset(e2017$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2016_here <- subset(e2016, e2016$cod_ibge == m$cod_ibge[i])
  e2017_here <- subset(e2017, e2017$cod_ibge == m$cod_ibge[i])
  et2016_here <- subset(et2016, et2016$cod_ibge == m$cod_ibge[i])
  et2017_here <- subset(et2017, et2017$cod_ibge == m$cod_ibge[i])
  ec2016_here <- subset(ec2016, ec2016$cod_ibge == m$cod_ibge[i])
  ec2017_here <- subset(ec2017, ec2017$cod_ibge == m$cod_ibge[i])
  if(nrow(e2016_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 3 & et2016_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 6 & et2016_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & et2016_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 3 & ec2016_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 6 & ec2016_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & ec2016_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2016_here[which(et2016_here$start_month < 10 & (et2016_here$end_month == 0 | et2016_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$start_month < 10 & (ec2016_here$end_month == 0 | ec2016_here$end_month > 6)),])
  }
  if(nrow(e2017_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$entry_type < 3 & et2017_here$start_month > 0 & et2017_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason %in% c(10,11) & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason == 21 & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$entry_type < 3 & ec2017_here$start_month > 0 & ec2017_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 10 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 21 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
  }
}

m2016 <- m

# Bind and export ---------------------------------------------------------

e <- rbind(m2004, m2008, m2012, m2016)

write_csv(e, "../../datasets/analysis/employment_lowpay_data.csv")

# Build employment dataset -- high-pay municipal employees --------------------------------------------

# These are the codes in CBO that correspond to managers. Codes can be checked at https://www.ocupacoes.com.br/cbo-mte/
managers_largecats <- c(1) # start with 1 all MEMBROS SUPERIORES DO PODER PÚBLICO, DIRIGENTES DE ORGANIZAÇÕES DE INTERESSE PÚBLICO E DE EMPRESAS, GERENTES

# 2004 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2004

e2004 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2004.csv") %>%
  dplyr::filter(mean_salary > median(mean_salary,na.rm=T))
et2004 <- e2004 %>% # Only temporary jobs
  subset(!(e2004$job_type %in% c(30,31)))
ec2004 <- e2004 %>% # Only tenured jobs
  subset(e2004$job_type %in% c(30,31))

e2005 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2005.csv") %>%
  dplyr::filter(mean_salary > median(mean_salary,na.rm=T))
et2005 <- e2005 %>% # Only temporary jobs
  subset(!(e2005$job_type %in% c(30,31)))
ec2005 <- e2005 %>% # Only tenured jobs
  subset(e2005$job_type %in% c(30,31))

# The letters "tem" correspond to temporary jobs, "con" to tenured jobs (for Portuguese "concursados")
# q14, q15 and q16 correspond to the 14th, 15th, and 16th quarter of a mayor's term, respectively (i.e., April - June, July - September, and October - December, respectively).
# q1 corresponds to the first quarter of the new administration (January to March)

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2004_here <- subset(e2004, e2004$cod_ibge == m$cod_ibge[i])
  e2005_here <- subset(e2005, e2005$cod_ibge == m$cod_ibge[i])
  et2004_here <- subset(et2004, et2004$cod_ibge == m$cod_ibge[i])
  et2005_here <- subset(et2005, et2005$cod_ibge == m$cod_ibge[i])
  ec2004_here <- subset(ec2004, ec2004$cod_ibge == m$cod_ibge[i])
  ec2005_here <- subset(ec2005, ec2005$cod_ibge == m$cod_ibge[i])
  if(nrow(e2004_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 3 & et2004_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 6 & et2004_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$entry_type < 3 & et2004_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason %in% c(10,11) & (et2004_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 3 & et2004_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & (et2004_here$end_month > 6 & et2004_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2004_here[which(et2004_here$end_reason == 21 & et2004_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 3 & ec2004_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 6 & ec2004_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$entry_type < 3 & ec2004_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 10 & (ec2004_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 3 & ec2004_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & (ec2004_here$end_month > 6 & ec2004_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2004_here[which(ec2004_here$end_reason == 21 & ec2004_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2004_here[which(et2004_here$start_month < 10 & (et2004_here$end_month == 0 | et2004_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2004_here[which(ec2004_here$start_month < 10 & (ec2004_here$end_month == 0 | ec2004_here$end_month > 6)),])
  }
  if(nrow(e2005_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$entry_type < 3 & et2005_here$start_month > 0 & et2005_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason %in% c(10,11) & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2005_here[which(et2005_here$end_reason == 21 & (et2005_here$end_month > 0 & et2005_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$entry_type < 3 & ec2005_here$start_month > 0 & ec2005_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 10 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2005_here[which(ec2005_here$end_reason == 21 & (ec2005_here$end_month > 0 & ec2005_here$end_month < 4)),])
  }
}

m2004 <- m

# 2008 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2008

e2008 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2008.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2008 <- e2008 %>%
  subset(!(e2008$job_type %in% c(30,31)))
ec2008 <- e2008 %>%
  subset(e2008$job_type %in% c(30,31))

e2009 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2009.csv") %>%
  dplyr::filter(mean_salary < median(mean_salary,na.rm=T))
et2009 <- e2009 %>%
  subset(!(e2009$job_type %in% c(30,31)))
ec2009 <- e2009 %>%
  subset(e2009$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2008_here <- subset(e2008, e2008$cod_ibge == m$cod_ibge[i])
  e2009_here <- subset(e2009, e2009$cod_ibge == m$cod_ibge[i])
  et2008_here <- subset(et2008, et2008$cod_ibge == m$cod_ibge[i])
  et2009_here <- subset(et2009, et2009$cod_ibge == m$cod_ibge[i])
  ec2008_here <- subset(ec2008, ec2008$cod_ibge == m$cod_ibge[i])
  ec2009_here <- subset(ec2009, ec2009$cod_ibge == m$cod_ibge[i])
  if(nrow(e2008_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 3 & et2008_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 6 & et2008_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$entry_type < 3 & et2008_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason %in% c(10,11) & (et2008_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 3 & et2008_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & (et2008_here$end_month > 6 & et2008_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2008_here[which(et2008_here$end_reason == 21 & et2008_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 3 & ec2008_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 6 & ec2008_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$entry_type < 3 & ec2008_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 10 & (ec2008_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 3 & ec2008_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & (ec2008_here$end_month > 6 & ec2008_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2008_here[which(ec2008_here$end_reason == 21 & ec2008_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2008_here[which(et2008_here$start_month < 10 & (et2008_here$end_month == 0 | et2008_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2008_here[which(ec2008_here$start_month < 10 & (ec2008_here$end_month == 0 | ec2008_here$end_month > 6)),])
  }
  if(nrow(e2009_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$entry_type < 3 & et2009_here$start_month > 0 & et2009_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason %in% c(10,11) & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2009_here[which(et2009_here$end_reason == 21 & (et2009_here$end_month > 0 & et2009_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$entry_type < 3 & ec2009_here$start_month > 0 & ec2009_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 10 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2009_here[which(ec2009_here$end_reason == 21 & (ec2009_here$end_month > 0 & ec2009_here$end_month < 4)),])
  }
}

m2008 <- m

# 2012 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2012

e2012 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2012.csv") %>%
  dplyr::filter(mean_salary > median(mean_salary,na.rm=T))
et2012 <- e2012 %>%
  subset(!(e2012$job_type %in% c(30,31)))
ec2012 <- e2012 %>%
  subset(e2012$job_type %in% c(30,31))

e2013 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2013.csv") %>%
  dplyr::filter(mean_salary > median(mean_salary,na.rm=T))
et2013 <- e2013 %>%
  subset(!(e2013$job_type %in% c(30,31)))
ec2013 <- e2013 %>%
  subset(e2013$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2012_here <- subset(e2012, e2012$cod_ibge == m$cod_ibge[i])
  e2013_here <- subset(e2013, e2013$cod_ibge == m$cod_ibge[i])
  et2012_here <- subset(et2012, et2012$cod_ibge == m$cod_ibge[i])
  et2013_here <- subset(et2013, et2013$cod_ibge == m$cod_ibge[i])
  ec2012_here <- subset(ec2012, ec2012$cod_ibge == m$cod_ibge[i])
  ec2013_here <- subset(ec2013, ec2013$cod_ibge == m$cod_ibge[i])
  if(nrow(e2012_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 3 & et2012_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 6 & et2012_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$entry_type < 3 & et2012_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason %in% c(10,11) & (et2012_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 3 & et2012_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & (et2012_here$end_month > 6 & et2012_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2012_here[which(et2012_here$end_reason == 21 & et2012_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 3 & ec2012_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 6 & ec2012_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$entry_type < 3 & ec2012_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 10 & (ec2012_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 3 & ec2012_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & (ec2012_here$end_month > 6 & ec2012_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2012_here[which(ec2012_here$end_reason == 21 & ec2012_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2012_here[which(et2012_here$start_month < 10 & (et2012_here$end_month == 0 | et2012_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2012_here[which(ec2012_here$start_month < 10 & (ec2012_here$end_month == 0 | ec2012_here$end_month > 6)),])
  }
  if(nrow(e2013_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$entry_type < 3 & et2013_here$start_month > 0 & et2013_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason %in% c(10,11) & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2013_here[which(et2013_here$end_reason == 21 & (et2013_here$end_month > 0 & et2013_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$entry_type < 3 & ec2013_here$start_month > 0 & ec2013_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 10 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2013_here[which(ec2013_here$end_reason == 21 & (ec2013_here$end_month > 0 & ec2013_here$end_month < 4)),])
  }

}

m2012 <- m

# 2016 cycle --------------------------------------------------------------

m <- read_csv("../../datasets/downloaded/other/basedosdados_municipality_identifiers.csv") %>%
  mutate(cod_ibge = id_municipio_6) %>%
  select(cod_ibge)
m$year <- 2016

e2016 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2016.csv") %>%
  dplyr::filter(mean_salary > median(mean_salary,na.rm=T))
et2016 <- e2016 %>%
  subset(!(e2016$job_type %in% c(30,31)))
ec2016 <- e2016 %>%
  subset(e2016$job_type %in% c(30,31))

e2017 <- read_csv("../../datasets/downloaded/employment/rais_processed/rais_municipal_employees_2017.csv") %>%
  dplyr::filter(mean_salary > median(mean_salary,na.rm=T))
et2017 <- e2017 %>%
  subset(!(e2017$job_type %in% c(30,31)))
ec2017 <- e2017 %>%
  subset(e2017$job_type %in% c(30,31))

m$hires_tem_q14 <- NA
m$hires_tem_q15 <- NA
m$hires_tem_q16 <- NA
m$hires_tem_q1 <- NA

m$fires_tem_q14 <- NA
m$fires_tem_q15 <- NA
m$fires_tem_q16 <- NA
m$fires_tem_q1 <- NA

m$resignations_tem_q14 <- NA
m$resignations_tem_q15 <- NA
m$resignations_tem_q16 <- NA
m$resignations_tem_q1 <- NA

m$hires_con_q14 <- NA
m$hires_con_q15 <- NA
m$hires_con_q16 <- NA
m$hires_con_q1 <- NA

m$fires_con_q14 <- NA
m$fires_con_q15 <- NA
m$fires_con_q16 <- NA
m$fires_con_q1 <- NA

m$resignations_con_q14 <- NA
m$resignations_con_q15 <- NA
m$resignations_con_q16 <- NA
m$resignations_con_q1 <- NA

m$stock_tem_q15 <- NA
m$stock_con_q15 <- NA

for(i in 1:nrow(m)){
  e2016_here <- subset(e2016, e2016$cod_ibge == m$cod_ibge[i])
  e2017_here <- subset(e2017, e2017$cod_ibge == m$cod_ibge[i])
  et2016_here <- subset(et2016, et2016$cod_ibge == m$cod_ibge[i])
  et2017_here <- subset(et2017, et2017$cod_ibge == m$cod_ibge[i])
  ec2016_here <- subset(ec2016, ec2016$cod_ibge == m$cod_ibge[i])
  ec2017_here <- subset(ec2017, ec2017$cod_ibge == m$cod_ibge[i])
  if(nrow(e2016_here) != 0){
    # Tem, hires
    m$hires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 3 & et2016_here$start_month < 7),])
    m$hires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 6 & et2016_here$start_month < 10),])
    m$hires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$entry_type < 3 & et2016_here$start_month > 9),])
    # Tem, fires
    m$fires_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$fires_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$fires_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason %in% c(10,11) & (et2016_here$end_month > 9)),])
    # Tem, resignations
    m$resignations_tem_q14[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 3 & et2016_here$end_month < 7)),])
    m$resignations_tem_q15[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & (et2016_here$end_month > 6 & et2016_here$end_month < 10)),])
    m$resignations_tem_q16[i] <- nrow(et2016_here[which(et2016_here$end_reason == 21 & et2016_here$end_month > 9),])
    # con, hires
    m$hires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 3 & ec2016_here$start_month < 7),])
    m$hires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 6 & ec2016_here$start_month < 10),])
    m$hires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$entry_type < 3 & ec2016_here$start_month > 9),])
    # con, fires
    m$fires_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$fires_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$fires_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 10 & (ec2016_here$end_month > 9)),])
    # con, resignations
    m$resignations_con_q14[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 3 & ec2016_here$end_month < 7)),])
    m$resignations_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & (ec2016_here$end_month > 6 & ec2016_here$end_month < 10)),])
    m$resignations_con_q16[i] <- nrow(ec2016_here[which(ec2016_here$end_reason == 21 & ec2016_here$end_month > 9),])
    # stocks
    m$stock_tem_q15[i] <- nrow(et2016_here[which(et2016_here$start_month < 10 & (et2016_here$end_month == 0 | et2016_here$end_month > 6)),])
    m$stock_con_q15[i] <- nrow(ec2016_here[which(ec2016_here$start_month < 10 & (ec2016_here$end_month == 0 | ec2016_here$end_month > 6)),])
  }
  if(nrow(e2017_here) != 0){
    # tem, hires
    m$hires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$entry_type < 3 & et2017_here$start_month > 0 & et2017_here$start_month < 4),])
    # tem, fires
    m$fires_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason %in% c(10,11) & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # tem, resignations
    m$resignations_tem_q1[i] <- nrow(et2017_here[which(et2017_here$end_reason == 21 & (et2017_here$end_month > 0 & et2017_here$end_month < 4)),])
    # con, hires
    m$hires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$entry_type < 3 & ec2017_here$start_month > 0 & ec2017_here$start_month < 4),])
    # con, fires
    m$fires_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 10 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
    # con, resignations
    m$resignations_con_q1[i] <- nrow(ec2017_here[which(ec2017_here$end_reason == 21 & (ec2017_here$end_month > 0 & ec2017_here$end_month < 4)),])
  }
}

m2016 <- m

# Bind and export ---------------------------------------------------------

e <- rbind(m2004, m2008, m2012, m2016)

write_csv(e, "../../datasets/analysis/employment_highpay_data.csv")

# Notes: R version, platform, and loaded packages -------------------------

sessionInfo(package = NULL)

# R version 4.2.1 (2022-06-23)
# Platform: aarch64-apple-darwin20 (64-bit)
# Running under: macOS Monterey 12.1
# 
# Matrix products: default
# LAPACK: /Library/Frameworks/R.framework/Versions/4.2-arm64/Resources/lib/libRlapack.dylib
# 
# locale:
#   [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
# 
# attached base packages:
#   [1] stats     graphics  grDevices utils     datasets  methods   base     
# 
# other attached packages:
#   [1] xtable_1.8-4    lubridate_1.8.0 here_1.0.1      forcats_0.5.2   stringr_1.5.0  
# [6] dplyr_1.1.2     purrr_0.3.4     readr_2.1.2     tidyr_1.2.0     tibble_3.2.1   
# [11] ggplot2_3.3.6   tidyverse_1.3.2
# 
# loaded via a namespace (and not attached):
#   [1] pillar_1.9.0        compiler_4.2.1      cellranger_1.1.0    dbplyr_2.2.1       
# [5] tools_4.2.1         jsonlite_1.8.0      googledrive_2.0.0   lifecycle_1.0.3    
# [9] gargle_1.2.0        gtable_0.3.1        pkgconfig_2.0.3     rlang_1.1.1        
# [13] reprex_2.0.2        cli_3.6.1           DBI_1.1.3           rstudioapi_0.14    
# [17] haven_2.5.1         xml2_1.3.3          withr_2.5.0         httr_1.4.4         
# [21] generics_0.1.3      vctrs_0.6.2         fs_1.5.2            hms_1.1.2          
# [25] rprojroot_2.0.3     googlesheets4_1.0.1 grid_4.2.1          tidyselect_1.2.0   
# [29] glue_1.6.2          R6_2.5.1            fansi_1.0.4         readxl_1.4.1       
# [33] modelr_0.1.9        tzdb_0.3.0          magrittr_2.0.3      backports_1.4.1    
# [37] scales_1.2.1        ellipsis_0.3.2      rvest_1.0.3         assertthat_0.2.1   
# [41] colorspace_2.0-3    utf8_1.2.3          stringi_1.7.12      munsell_0.5.0      
# [45] broom_1.0.1         crayon_1.5.1       